﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Diagnostics;

using DataConDemoWebRole.Models;
using System.Data.SqlClient;
using System.Configuration;
using System.Xml.Linq;
using System.IO;
using Microsoft.SqlServer.Types;
namespace DataConDemoWebRole.Business
{
    /// <summary>
    /// This is a lightweight class that handles all data retrieval.
    /// </summary>
    public class DataService
    {
        /// <summary>
        /// These names match the names of the checkboxes in ControlPanel.
        /// </summary>
        public static class TableName
        {
            public const string Countries = "Countries";
            public const string Counties = "Counties";
            public const string States = "States";
            public const string Faults = "Faults";
            public const string Earthquakes = "Earthquakes";
        }

        /// <summary>
        /// For each table in the query, return a FeatureCollection.
        /// </summary>
        /// <param name="query">query with a bounding box</param>
        /// <returns>set of GeoJson Features</returns>
        public MapDataModel GetData(BoundingBoxQuery query)
        {
            var data = new MapDataModel();
            var start = DateTime.Now;
            try
            {
                if (query.Tables.Contains(TableName.Counties))
                {
                    data.featureSet.Add(TableName.Counties, GetCounties(query.Box, query.Shape, query.Distance));
                }
                if (query.Tables.Contains(TableName.Countries))
                {
                    data.featureSet.Add(TableName.Countries, GetCountries(query.Box, query.Shape, query.Distance));
                }
                if (query.Tables.Contains(TableName.Earthquakes))
                {
                    data.featureSet.Add(TableName.Earthquakes, GetEarthquakes(query.Box, query.Shape, query.Distance));
                }
                if (query.Tables.Contains(TableName.Faults))
                {
                    data.featureSet.Add(TableName.Faults, GetFaults(query.Box, query.Shape, query.Distance));
                }
                if (query.Tables.Contains(TableName.States))
                {
                    data.featureSet.Add(TableName.States, GetStates(query.Box, query.Shape, query.Distance));
                }
            }
            catch (Exception ex)
            {
                data = new MapDataModel() { error = "Something went wrong : " + ex.Message };
                Debug.WriteLine(data.error);
            }

            // Calculate metrics to return.
            var elapsed = DateTime.Now - start;

            // This counting of the number of points is based on the simplified geometries being returned.
            // For example, only the points in the first ring of polygons is counted since we only use the first ring
            // (which is another name for the outside border).
            // The number of points is not needed for drawing, it is just being returned in case you are interested.
            // If you like, you can extend the counting functions below.
            data.metrics.NumberOfFeatures = data.featureSet.Values.Select(fc => fc.features.Length).Sum();

            data.metrics.NumberOfPoints += data.featureSet.Values.SelectMany(fc => fc.features).Select(f => f.geometry).OfType<Models.GeoJson.Point>().Count();
            data.metrics.NumberOfPoints += data.featureSet.Values.SelectMany(fc => fc.features).Select(f => f.geometry).OfType<Models.GeoJson.MultiPoint>().Select(mp => mp.coordinates.Length).Sum();
            data.metrics.NumberOfPoints += data.featureSet.Values.SelectMany(fc => fc.features).Select(f => f.geometry).OfType<Models.GeoJson.LineString>().Select(ls => ls.coordinates.Length).Sum();
            data.metrics.NumberOfPoints += data.featureSet.Values.SelectMany(fc => fc.features).Select(f => f.geometry).OfType<Models.GeoJson.MultiLineString>().SelectMany(mls => mls.coordinates).Select(a => a.Length).Sum();
            data.metrics.NumberOfPoints += data.featureSet.Values.SelectMany(fc => fc.features).Select(f => f.geometry).OfType<Models.GeoJson.Polygon>().Select(p => p.coordinates[0].Length).Sum();
            data.metrics.NumberOfPoints += data.featureSet.Values.SelectMany(fc => fc.features).Select(f => f.geometry).OfType<Models.GeoJson.MultiPolygon>().SelectMany(p => p.coordinates).Select(pa => pa[0].Length).Sum();
            
            data.metrics.QueryTimeMilliseconds = (int)elapsed.TotalMilliseconds;
            return data;
        }

        
        /// <summary>
        /// Get the States as GeoJSON MultiPolygons.
        /// </summary>
        /// <param name="box">bounding box</param>
        /// <param name="geo">optional intersecting geometry</param>
        /// <param name="distance">optional distance</param>
        /// <returns>States in GeoJSON format</returns>
        private Models.GeoJson.FeatureCollection GetStates(SqlGeography box, SqlGeography geo, double? distance)
        {
            var features = new List<Models.GeoJson.Feature>();

            using (var conn = GetOpenConnection())
            {
                using (var cmd = new SqlCommand("usp_StatesByIntersection", conn))
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@Box", System.Data.SqlDbType.Udt)
                    {
                        UdtTypeName = "geography",
                        Value = box
                    });
                    if (geo != null)
                    {
                        cmd.Parameters.Add(new SqlParameter("@Geo", System.Data.SqlDbType.Udt)
                        {
                            UdtTypeName = "geography",
                            Value = geo
                        });
                    }
                    if (distance.HasValue)
                    {
                        cmd.CommandText = "usp_StatesByDistance";
                        cmd.Parameters.AddWithValue("Distance", distance);
                    }

                    using (var rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            // get geography and translate to GeoJson
                            var geography = (SqlGeography)rdr["the_geo"];
                            var geometry = GeoJsonConvert.GeoJsonMultiPolygonFromGeography(geography);

                            // get properties as strings
                            var properties = new Dictionary<string, string>();
                            properties.Add("NAME", rdr["NAME"].ToString());
                            properties.Add("NAME_0", rdr["NAME_0"].ToString());
                            properties.Add("ISO", rdr["ISO"].ToString());

                            var feature = new Models.GeoJson.Feature()
                            {
                                geometry = geometry,
                                properties = properties
                            };
                            features.Add(feature);
                        }
                    }
                }
            }

            var fc = new Models.GeoJson.FeatureCollection()
            {
                features = features.ToArray()
            };
            return fc;
        }


        /// <summary>
        /// Get the Faults as GeoJSON MultiLineString features.
        /// </summary>
        /// <param name="box">bounding box</param>
        /// <param name="geo">optional intersecting geometry</param>
        /// <param name="distance">optional distance</param>
        /// <returns>Faults in GeoJSON format</returns>
        private Models.GeoJson.FeatureCollection GetFaults(SqlGeography box, SqlGeography geo, double? distance)
        {
            var features = new List<Models.GeoJson.Feature>();

            using (var conn = GetOpenConnection())
            {
                using (var cmd = new SqlCommand("usp_FaultsByIntersection", conn))
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@Box", System.Data.SqlDbType.Udt)
                    {
                        UdtTypeName = "geography",
                        Value = box
                    });
                    if (geo != null)
                    {
                        cmd.Parameters.Add(new SqlParameter("@Geo", System.Data.SqlDbType.Udt)
                        {
                            UdtTypeName = "geography",
                            Value = geo
                        });
                    }
                    if (distance.HasValue)
                    {
                        cmd.CommandText = "usp_FaultsByDistance";
                        cmd.Parameters.AddWithValue("Distance", distance);
                    }

                    using (var rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            // get geography and translate to GeoJson
                            var geography = (SqlGeography)rdr["the_geo"];
                            var geometry = GeoJsonConvert.GeoJsonMultiLineStringFromGeography(geography);

                            // get properties as strings
                            var properties = new Dictionary<string, string>();
                            properties.Add("NAME", rdr["NAME"].ToString());
                            properties.Add("CODE", rdr["CODE"].ToString());
                            properties.Add("SLIP", rdr["SLIP"].ToString());
                            properties.Add("NUM", rdr["NUM"].ToString());
                            properties.Add("AGE", rdr["AGE"].ToString());

                            var feature = new Models.GeoJson.Feature()
                            {
                                geometry = geometry,
                                properties = properties
                            };
                            features.Add(feature);
                        }
                    }
                }
            }

            var fc = new Models.GeoJson.FeatureCollection()
            {
                features = features.ToArray()
            };
            return fc;
        }

 

        /// <summary>
        /// Get the Countries as GeoJSON MultiPolygon features.
        /// </summary>
        /// <param name="box">bounding box</param>
        /// <param name="geo">optional intersecting geometry</param>
        /// <param name="distance">optional distance</param>
        /// <returns>Countries in GeoJSON format</returns>
        private Models.GeoJson.FeatureCollection GetCountries(SqlGeography box, SqlGeography geo, double? distance)
        {
            var features = new List<Models.GeoJson.Feature>();

            using (var conn = GetOpenConnection())
            {
                using (var cmd = new SqlCommand("usp_CountriesByIntersection", conn))
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@Box", System.Data.SqlDbType.Udt)
                    {
                        UdtTypeName = "geography",
                        Value = box
                    });
                    if (geo != null)
                    {
                        cmd.Parameters.Add(new SqlParameter("@Geo", System.Data.SqlDbType.Udt)
                        {
                            UdtTypeName = "geography",
                            Value = geo
                        });
                    }
                    if (distance.HasValue)
                    {
                        cmd.CommandText = "usp_CountriesByDistance";
                        cmd.Parameters.AddWithValue("Distance", distance);
                    }

                    using (var rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            // get geography and translate to GeoJson
                            var geography = (SqlGeography)rdr["the_geo"];
                            var geometry = GeoJsonConvert.GeoJsonMultiPolygonFromGeography(geography);

                            // get properties as strings
                            var properties = new Dictionary<string, string>();
                            properties.Add("NAME", rdr["NAME"].ToString());
                            properties.Add("SOV", rdr["SOV"].ToString());

                            var feature = new Models.GeoJson.Feature()
                            {
                                geometry = geometry,
                                properties = properties
                            };
                            features.Add(feature);
                        }
                    }
                }
            }

            var fc = new Models.GeoJson.FeatureCollection()
            {
                features = features.ToArray()
            };
            return fc;
        }

 

        /// <summary>
        /// Get the Counties as GeoJSON MultiPolygon features.
        /// </summary>
        /// <param name="box">bounding box</param>
        /// <param name="geo">optional intersecting geometry</param>
        /// <param name="distance">optional distance</param>
        /// <returns>Counties in GeoJSON format</returns>
        private Models.GeoJson.FeatureCollection GetCounties(SqlGeography box, SqlGeography geo, double? distance)
        {
            var features = new List<Models.GeoJson.Feature>();

            using (var conn = GetOpenConnection())
            {
                using (var cmd = new SqlCommand("usp_CountiesByIntersection", conn))
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@Box", System.Data.SqlDbType.Udt)
                    {
                        UdtTypeName = "geography",
                        Value = box
                    });
                    if (geo != null)
                    {
                        cmd.Parameters.Add(new SqlParameter("@Geo", System.Data.SqlDbType.Udt)
                        {
                            UdtTypeName = "geography",
                            Value = geo
                        });
                    }
                    if (distance.HasValue)
                    {
                        cmd.CommandText = "usp_CountiesByDistance";
                        cmd.Parameters.AddWithValue("Distance", distance);
                    }

                    using (var rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            // get geography and translate to GeoJson
                            var geography = (SqlGeography)rdr["the_geo"];
                            var geometry = GeoJsonConvert.GeoJsonMultiPolygonFromGeography(geography);

                            // get properties as strings
                            var properties = new Dictionary<string, string>();
                            properties.Add("NAME", rdr["NAME"].ToString());
                            properties.Add("STATE", rdr["STATE"].ToString());
                            properties.Add("AREA", rdr["AREA"].ToString());

                            var feature = new Models.GeoJson.Feature()
                            {
                                geometry = geometry,
                                properties = properties
                            };
                            features.Add(feature);
                        }
                    }
                }
            }

            var fc = new Models.GeoJson.FeatureCollection()
            {
                features = features.ToArray()
            };
            return fc;
        }

        /// <summary>
        /// Get the Earthquakes as GeoJSON Point features.
        /// </summary>
        /// <param name="box">bounding box</param>
        /// <param name="geo">optional intersecting geometry</param>
        /// <param name="distance">optional distance</param>
        /// <returns>Earthquakes in GeoJSON format</returns>
        private Models.GeoJson.FeatureCollection GetEarthquakes(SqlGeography box, SqlGeography geo, double? distance)
        {
            var features = new List<Models.GeoJson.Feature>();

            using (var conn = GetOpenConnection())
            {
                using (var cmd = new SqlCommand("usp_EarthquakesByIntersection", conn))
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@Box", System.Data.SqlDbType.Udt)
                    {
                        UdtTypeName = "geography",
                        Value = box
                    });
                    if (geo != null)
                    {
                        cmd.Parameters.Add(new SqlParameter("@Geo", System.Data.SqlDbType.Udt)
                        {
                            UdtTypeName = "geography",
                            Value = geo
                        });
                    }
                    if (distance.HasValue)
                    {
                        cmd.CommandText = "usp_EarthquakesByDistance";
                        cmd.Parameters.AddWithValue("Distance", distance);
                    }

                    using (var rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            // get geography and translate to GeoJson
                            var geography = (SqlGeography)rdr["the_geo"];
                            var point = GeoJsonConvert.GeoJsonPointFromGeography(geography);

                            // get properties as strings
                            var properties = new Dictionary<string, string>();
                            properties.Add("YEAR", rdr["YEAR"].ToString());
                            properties.Add("NAME", rdr["NAME"].ToString());
                            properties.Add("MMIO", rdr["MMIO"].ToString());

                            var feature = new Models.GeoJson.Feature()
                            {
                                geometry = point,
                                properties = properties
                            };
                            features.Add(feature);
                        }
                    }
                }
            }

            var fc = new Models.GeoJson.FeatureCollection()
            {
                features = features.ToArray()
            };
            return fc;
        }

        /// <summary>
        /// Get an open connection using the configured connection string.
        /// </summary>
        /// <returns></returns>
        private SqlConnection GetOpenConnection()
        {
            string connStr = ConfigurationManager.ConnectionStrings["SampleData"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            return conn;
        }

        #region Tile methods

        /// <summary>
        /// Tiling requires a geography and an associated data value used for thematic coloring.
        /// </summary>
        public class TileGeo
        {
            /// <summary>
            /// geography for a feature
            /// </summary>
            public SqlGeography Geography { get; set; }

            /// <summary>
            /// data value for a feature, used to color when thematic mapping
            /// </summary>
            public double DataValue { get; set; }
        }


        /// <summary>
        /// Get the geographies and data values for the counties within a bounding box.
        /// </summary>
        /// <param name="box">bounding box</param>
        /// <param name="reduce">reduction factor</param>
        /// <returns>geographies for counties</returns>
        public List<TileGeo> GetTileGeographiesCounties(SqlGeography box, double reduce)
        {
            var tiles = new List<TileGeo>();
            using (var conn = GetOpenConnection())
            {
                using (var cmd = new SqlCommand("usp_CountiesByIntersection", conn))
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@Box", System.Data.SqlDbType.Udt)
                    {
                        UdtTypeName = "geography",
                        Value = box
                    });

                    using (var rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            var geography = (SqlGeography)rdr["the_geo"];
                            if (reduce > 0)
                            {
                                geography = geography.Reduce(reduce);
                            }
                            var tile = new TileGeo()
                            {
                                Geography = geography,
                                DataValue = Convert.ToDouble(rdr["AREA"])
                            };
                            tiles.Add(tile);
                        }
                    }
                }
            }
            return tiles;
        }
        /// <summary>
        /// Get the geographies and data values for the countries within a bounding box.
        /// </summary>
        /// <param name="box">bounding box</param>
        /// <param name="reduce">reduction factor</param>
        /// <returns>geographies for countries</returns>
        public List<TileGeo> GetTileGeographiesCountries(SqlGeography box, double reduce)
        {
            var tiles = new List<TileGeo>();
            using (var conn = GetOpenConnection())
            {
                using (var cmd = new SqlCommand("usp_CountriesByIntersection", conn))
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@Box", System.Data.SqlDbType.Udt)
                    {
                        UdtTypeName = "geography",
                        Value = box
                    });

                    using (var rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            var geography = (SqlGeography)rdr["the_geo"];
                            if (reduce > 0)
                            {
                                geography = geography.Reduce(reduce);
                            }
                            var tile = new TileGeo()
                            {
                                Geography = geography,
                                DataValue = Convert.ToDouble(rdr["AREA"])
                            };
                            tiles.Add(tile);
                        }
                    }
                }
            }
            return tiles;
        }

        /// <summary>
        /// Get the geographies and data values for the states within a bounding box.
        /// </summary>
        /// <param name="box">bounding box</param>
        /// <param name="reduce">reduction factor</param>
        /// <returns>geographies for states</returns>
        public List<TileGeo> GetTileGeographiesStates(SqlGeography box, double reduce)
        {
            var tiles = new List<TileGeo>();
            using (var conn = GetOpenConnection())
            {
                using (var cmd = new SqlCommand("usp_StatesByIntersection", conn))
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@Box", System.Data.SqlDbType.Udt)
                    {
                        UdtTypeName = "geography",
                        Value = box
                    });

                    using (var rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            var geography = (SqlGeography)rdr["the_geo"];
                            if (reduce > 0)
                            {
                                geography = geography.Reduce(reduce);
                            }
                            var tile = new TileGeo()
                            {
                                Geography = geography,
                                DataValue = Convert.ToDouble(rdr["AREA"])
                            };
                            tiles.Add(tile);
                        }
                    }
                }
            }
            return tiles;
        }

        /// <summary>
        /// Get the geographies and data values for the faults within a bounding box.
        /// </summary>
        /// <param name="box">bounding box</param>
        /// <param name="reduce">reduction factor</param>
        /// <returns>geographies for faults</returns>
        public List<TileGeo> GetTileGeographiesFaults(SqlGeography box, double reduce)
        {
            var tiles = new List<TileGeo>();
            using (var conn = GetOpenConnection())
            {
                using (var cmd = new SqlCommand("usp_FaultsByIntersection", conn))
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@Box", System.Data.SqlDbType.Udt)
                    {
                        UdtTypeName = "geography",
                        Value = box
                    });

                    using (var rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            var geography = (SqlGeography)rdr["the_geo"];
                            if (reduce > 0)
                            {
                                geography = geography.Reduce(reduce);
                            }
                            var tile = new TileGeo()
                            {
                                Geography = geography,
                                DataValue = Convert.ToDouble(rdr["ACODE"])
                            };
                            tiles.Add(tile);
                        }
                    }
                }
            }
            return tiles;
        }

        /// <summary>
        /// Get the geographies and data values for the Earthquakes within a bounding box.
        /// </summary>
        /// <param name="box">bounding box</param>
        /// <param name="reduce">reduction factor</param>
        /// <returns>geographies for Earthquakes</returns>
        public List<TileGeo> GetTileGeographiesEarthquakes(SqlGeography box, double reduce)
        {
            var tiles = new List<TileGeo>();
            using (var conn = GetOpenConnection())
            {
                using (var cmd = new SqlCommand("usp_EarthquakesByIntersection", conn))
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@Box", System.Data.SqlDbType.Udt)
                    {
                        UdtTypeName = "geography",
                        Value = box
                    });

                    using (var rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            var geography = (SqlGeography)rdr["the_geo"];
                            if (reduce > 0)
                            {
                                geography = geography.Reduce(reduce);
                            }
                            var tile = new TileGeo()
                            {
                                Geography = geography,
                                DataValue = Convert.ToDouble(rdr["OTHER_MAG1"])
                            };
                            tiles.Add(tile);
                        }
                    }
                }
            }
            return tiles;
        }

        #endregion
    }
}